{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fb562f66-622e-468d-ad8f-41a42f69d203",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts.globals import CurrentConfig, NotebookType\n",
    "CurrentConfig.NOTEBOOK_TYPE = NotebookType.JUPYTER_LAB"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a0d7d6a5-8e6b-4373-939c-939ea1a903de",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = spark.read.csv(\"hdfs://node1:8020/input/datasets/lianjia_seconds.csv\", header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a23a8c07-774a-4a40-bd1c-f73130ceb60a",
   "metadata": {},
   "outputs": [],
   "source": [
    "count = df.groupBy(\"所在区域\").count().orderBy(\"count\", ascending=False)\n",
    "count.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7fbc5a6f-0329-4dde-9b8b-9a2602482812",
   "metadata": {},
   "outputs": [],
   "source": [
    "xaxis_data = count.select(\"所在区域\").collect()\n",
    "yaxis_data = count.select(\"count\").rdd.flatMap(lambda x: x).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "82424844-5aed-421e-9feb-6c4f0f141d31",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts.charts import Bar\n",
    "import pyecharts.options as opts\n",
    "\n",
    "bar = Bar() \\\n",
    "        .add_xaxis(xaxis_data) \\\n",
    "        .add_yaxis(\"房源总数\", yaxis_data) \\\n",
    "        .set_global_opts(\n",
    "            xaxis_opts=opts.AxisOpts(\n",
    "                axislabel_opts={\"interval\":\"0\", \"rotate\":45}\n",
    "            )\n",
    "        )\n",
    "bar.load_javascript()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d14ae18e-b666-4ce8-974e-a1c7f9d41f24",
   "metadata": {},
   "outputs": [],
   "source": [
    "bar.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8374419f-491c-44fd-8873-0dfdf74b95eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import collect_list\n",
    "\n",
    "price = df.groupBy(\"所在区域\").agg(collect_list(\"房屋单价\").alias(\"price\"))\n",
    "price.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1681cdac-4ca4-4632-bd93-41f178e60bfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "xaxis_data = price.select(\"所在区域\").collect()\n",
    "prices = price.select(\"price\").rdd.flatMap(lambda x: x).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e9092549-9cfc-4230-8223-98628ea98109",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts.charts import Boxplot\n",
    "import pyecharts.options as opts\n",
    "\n",
    "yaxis_data = []\n",
    "for p in prices:\n",
    "    yaxis_data.append(list(map(int, list(p))))\n",
    "\n",
    "boxplot = Boxplot()\n",
    "boxplot.add_xaxis(xaxis_data)\n",
    "boxplot.add_yaxis(\"房屋单价\", boxplot.prepare_data(yaxis_data))\n",
    "boxplot.set_global_opts(\n",
    "        xaxis_opts=opts.AxisOpts(\n",
    "            axislabel_opts={\"interval\":\"0\", \"rotate\":45}\n",
    "        )\n",
    "    )\n",
    "\n",
    "boxplot.load_javascript()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e06244eb-639a-4fac-84b2-beb7a0880c91",
   "metadata": {},
   "outputs": [],
   "source": [
    "boxplot.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17d69521-f2b1-4f5c-8547-1f44a1b0416e",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_pair = df.groupBy(\"装修情况\").count().rdd.map(lambda x:(x[0],x[1])).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23693d9e-6cf3-45ce-accd-66e56b183e05",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts.charts import Pie\n",
    "import pyecharts.options as opts\n",
    "\n",
    "pie = Pie()\n",
    "pie.add(series_name=\"房屋装修情况\",\n",
    "        data_pair=data_pair)\n",
    "\n",
    "pie.load_javascript()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bddfb537-9c4b-4db8-89c8-ad263fe95a12",
   "metadata": {},
   "outputs": [],
   "source": [
    "pie.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bdf53ed-691c-437f-bf99-044719bd6676",
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = df.groupBy(\"所在区域\").count().select(\"所在区域\")\n",
    "df2 = df.groupBy(\"装修情况\").count().select(\"装修情况\")\n",
    "df3 = df1.join(df2).join(df, [\"所在区域\",\"装修情况\"], how=\"leftOuter\")\n",
    "\n",
    "xaxis_data = df3.groupBy(\"所在区域\").count().orderBy(\"所在区域\").select(\"所在区域\").collect()\n",
    "\n",
    "count = df3.groupBy(\"所在区域\",\"装修情况\").count().orderBy(\"所在区域\")\n",
    "\n",
    "z1 = count.select(\"count\").where(\"`装修情况` = '精装'\").rdd.flatMap(lambda x: x).collect()\n",
    "z2 = count.select(\"count\").where(\"`装修情况` = '简装'\").rdd.flatMap(lambda x: x).collect()\n",
    "z3 = count.select(\"count\").where(\"`装修情况` = '毛坯'\").rdd.flatMap(lambda x: x).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3aab99f9-6548-4139-861e-019a83e9148e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts.charts import Bar\n",
    "import pyecharts.options as opts\n",
    "\n",
    "bar = Bar() \\\n",
    "        .add_xaxis(xaxis_data) \\\n",
    "        .add_yaxis(\"精装\", z1) \\\n",
    "        .add_yaxis(\"简装\", z2) \\\n",
    "        .add_yaxis(\"毛坯\", z3) \\\n",
    "        .extend_axis(yaxis=opts.AxisOpts()) \\\n",
    "        .set_global_opts(\n",
    "            xaxis_opts=opts.AxisOpts(\n",
    "                axislabel_opts={\"interval\":\"0\", \"rotate\":45}\n",
    "            )\n",
    "        )\n",
    "\n",
    "\n",
    "\n",
    "bar.load_javascript()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dc53f131-113d-42d0-9abc-d7595c4f4f2b",
   "metadata": {},
   "outputs": [],
   "source": [
    "bar.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2b5f907b-a286-496a-9fcb-5eacfbd6fc7b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.createOrReplaceTempView(\"Lianjia\")\n",
    "\n",
    "df1 = spark.sql('select a.`所在区域`, b.`装修情况`, sum(case when c.`房屋单价` is null then 0 else 1 end) as count from (select `所在区域` from lianjia group by `所在区域`) a left join (select `装修情况` from lianjia group by `装修情况`) b left join lianjia c on a.`所在区域` = c.`所在区域` and b.`装修情况` = c.`装修情况` group by a.`所在区域`, b.`装修情况`')\n",
    "df1.cache()\n",
    "df2 = spark.sql('select a.`所在区域`, b.`装修情况`, cast(round(mean(nvl(c.`房屋单价`,0))) as int) as mean from (select `所在区域` from lianjia group by `所在区域`) a left join (select `装修情况` from lianjia group by `装修情况`) b left join lianjia c on a.`所在区域` = c.`所在区域` and b.`装修情况` = c.`装修情况` group by a.`所在区域`, b.`装修情况`')\n",
    "df2.cache()\n",
    "\n",
    "xaxis_data = df1.groupBy(\"所在区域\").count().orderBy(\"所在区域\").select(\"所在区域\").rdd.flatMap(lambda x: x).collect()\n",
    "\n",
    "b1=df1.select(\"count\").where(\"`装修情况` = '精装'\").orderBy(\"所在区域\").rdd.flatMap(lambda x: x).collect()\n",
    "b2=df1.select(\"count\").where(\"`装修情况` = '简装'\").orderBy(\"所在区域\").rdd.flatMap(lambda x: x).collect()\n",
    "b3=df1.select(\"count\").where(\"`装修情况` = '毛坯'\").orderBy(\"所在区域\").rdd.flatMap(lambda x: x).collect()\n",
    "\n",
    "l1=df2.select(\"mean\").where(\"`装修情况` = '精装'\").orderBy(\"所在区域\").rdd.flatMap(lambda x: x).collect()\n",
    "l2=df2.select(\"mean\").where(\"`装修情况` = '简装'\").orderBy(\"所在区域\").rdd.flatMap(lambda x: x).collect()\n",
    "l3=df2.select(\"mean\").where(\"`装修情况` = '毛坯'\").orderBy(\"所在区域\").rdd.flatMap(lambda x: x).collect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97a64522-7037-4cf1-a35e-2b26de8b378e",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyecharts.charts import Bar, Line\n",
    "import pyecharts.options as opts\n",
    "\n",
    "bar = (Bar() \\\n",
    "        .add_xaxis(xaxis_data) \\\n",
    "        .add_yaxis(\"精装\", b1) \\\n",
    "        .add_yaxis(\"简装\", b2) \\\n",
    "        .add_yaxis(\"毛坯\", b3) \\\n",
    "        .extend_axis(yaxis=opts.AxisOpts()) \\\n",
    "        .set_series_opts(label_opts=opts.LabelOpts(is_show=False)) \\\n",
    "        .set_global_opts(\n",
    "            xaxis_opts=opts.AxisOpts(\n",
    "                axislabel_opts={\"interval\":\"0\", \"rotate\":45}\n",
    "            ),\n",
    "            yaxis_opts=opts.AxisOpts(max_ = 1000)\n",
    "        ))\n",
    "\n",
    "line = (Line() \\\n",
    "        .add_xaxis(xaxis_data) \\\n",
    "        .add_yaxis(\"精装\", l1, yaxis_index=1) \\\n",
    "        .add_yaxis(\"简装\", l2, yaxis_index=1) \\\n",
    "        .add_yaxis(\"毛坯\", l3, yaxis_index=1)) \\\n",
    "        .set_series_opts(label_opts=opts.LabelOpts(is_show=False)) \\\n",
    "\n",
    "bar.overlap(line)\n",
    "bar.load_javascript()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd5e9d99-8295-4ca9-8186-8e6647e926fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "bar.render_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77b32195-6d62-4700-a10f-f44382878b41",
   "metadata": {},
   "outputs": [],
   "source": [
    "from snownlp import SnowNLP\n",
    "from pyspark.sql import functions as F\n",
    "from pyspark.sql.types import ArrayType, StringType\n",
    "\n",
    "# 自定义函数用于中文分词\n",
    "def seg_sentence(content):\n",
    "    return [i for i in SnowNLP(content).words]\n",
    "\n",
    "# 注册自定义函数\n",
    "seg_sentence=F.udf(seg_sentence,ArrayType(StringType()))\n",
    "\n",
    "words = \" \".join(df.where(\"`核心卖点` is not null\").select(seg_sentence('核心卖点').alias(\"words\")).rdd.flatMap(lambda x: x.words).collect())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e177f43a-78f9-4ae1-940b-862c2b20e3d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "from stylecloud import gen_stylecloud\n",
    "\n",
    "# 生成词云图\n",
    "gen_stylecloud(\n",
    "    text=words,size=(800,600),icon_name='fas fa-home',\n",
    "    max_font_size=100,max_words=2000,\n",
    "    output_name='Lianjia.png',\n",
    "    font_path=\"../../SimHei.ttf\",collocations=True\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "560e9db2-fe32-40d8-b219-a8fbce221eec",
   "metadata": {},
   "source": [
    "![](Lianjia.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
